package org.firefly.db.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.RandomStringUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.firefly.db.excel.ExcelData;
import org.soul.util.ChineseUtils;

/**
 * excel工具类
 * 
 * @author firefly
 *
 */
public class ExcelUtils {

	/**
	 * 解析导入文件为一个ImportExcel
	 * 
	 * @param f
	 * @param expPropertiesList
	 * @return
	 * @throws Exception 
	 */
	public static ExcelData processImortData(File uf, ExcelData ed ,Map<String,String> titleColumn) throws Exception {
	    Workbook wb = readExcel(uf);
		// int sheetNumber = wb.getNumberOfSheets();
		Sheet sheet = wb.getSheetAt(0);
		int titleRowIndex = 0; // 标题行号,默认为第一行
		int colNum = sheet.getRow(titleRowIndex).getPhysicalNumberOfCells(); // 有效列数
		if (colNum < 1) {
			throw new Exception("没有有效列");
		}
		Row titleRow = sheet.getRow(titleRowIndex);// 首行为标题行
		Set<String> names = new HashSet<>();
		// 构建首行对应关系
		for (int i = 0; i < colNum; i++) {
			Cell c = titleRow.getCell(i);
			String cStr = getStringCellVal(c);
			if(StringUtils.isBlank(cStr)){
			    continue;
			}
			String columnName = null;
			if(titleColumn != null){
			   columnName = titleColumn.get(cStr);
			}
			if(columnName == null){
			    columnName = ChineseUtils.getPinYinFirstLetter(cStr);
			}
			while(names.contains(columnName)){//防止重名
			    String sufix = RandomStringUtils.random(3, '1','2','3','4','5','6','7','8','9','0');//最后3位随机数防止表名重复
			    columnName = columnName + "_" + sufix;
			}
			names.add(columnName);
			ed.addColumn(i, cStr, columnName);
			ed.setColumnType(i, Types.VARCHAR);//TODO 根据excel第一数据行类型设置数据类型,当前都是varchar
		}
		
		
		setExcelData(ed, titleRowIndex + 1, sheet, colNum);
		return ed;
	}
	
	/**
     * 读取excel文件
     * 
     * @param excelFilePath
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     */
    private static Workbook readExcel(File excelFile) {
        Workbook wb = null;
        try {
            if (excelFile.getName().endsWith(".xlsx")) {
                wb = new XSSFWorkbook(new FileInputStream(excelFile));
            } else if (excelFile.getName().endsWith(".xls")) {
                POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelFile));
                wb = new HSSFWorkbook(fs);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

	/**
	 * 获取excel中的数据 二维数据的行列与excel相同,跳过标题行，从数据行开始，数据首行行号为0
	 * @param dataRowNum 数据首行编号
	 * @param sheet 
	 * @param colNum 有效列数
	 * @return
	 */
	private static void setExcelData(ExcelData ed ,int dataRowNum, Sheet sheet, int colNum) {
		int maxRowSize = sheet.getPhysicalNumberOfRows();
		Row r = null;
		for (int rowIndex = dataRowNum; rowIndex < maxRowSize; rowIndex++) {
			r = sheet.getRow(rowIndex);
			for (int j = 0; j < colNum; j++) {
				Cell cell = r.getCell(j);
				if (cell != null) {
					String value = getStringCellVal(cell);
					ed.setCellVal(rowIndex - dataRowNum, j, value);
				}else{
				    ed.setCellVal(rowIndex - dataRowNum, j, null);
				}
			}
		}
	}
	
	/**
     * 获得单元格的值
     * 
     * @param c
     * @return
     */
    public static String getStringCellVal(Cell c) {
        if (c == null)
            return null;
        if (Cell.CELL_TYPE_STRING == c.getCellType()) {
            return c.getStringCellValue();
        } else if (Cell.CELL_TYPE_NUMERIC == c.getCellType()) {
            if (HSSFDateUtil.isCellDateFormatted(c)) {
                Date d = c.getDateCellValue();
                if (d != null)
                    return new SimpleDateFormat("yyyy-MM-dd").format(d);
            } else {
                return String.valueOf(c.getNumericCellValue());
            }
        } else if (Cell.CELL_TYPE_BOOLEAN == c.getCellType()) {
            return c.getBooleanCellValue() ? "是" : "否";
        }
        return null;
    }
}
